package usf.vend.a.bull.db;

import java.io.IOException;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class VendabullDBM
{
	private Context context;
	private SQLiteDatabase database;
	private VendabullDBH dbHelper;
	
	public static final String COLLEGES_TABLE = "colleges";
	private static final String COLLEGES_ROWID = "_id";
	private static final String COLLEGES_COLLEGE = "college";
	private static final String COLLEGES_LAT = "lat";
	private static final String COLLEGES_LONG = "lng";
	private static final String COLLEGES_COLUMN[] = new String[] {COLLEGES_ROWID, COLLEGES_COLLEGE, COLLEGES_LAT, COLLEGES_LONG};
	
	public static final String BUILDINGS_TABLE = "buildings";
	private static final String BUILDINGS_ROWID = "_id";
	private static final String BUILDINGS_COLLEGE_ID = "college_id";
	private static final String BUILDINGS_BUILDING = "building";
	private static final String BUILDINGS_NAME = "name";
	private static final String BUILDINGS_LAT = "lat";
	private static final String BUILDINGS_LONG = "lng";
	private static final String BUILDINGS_COLUMN[] = new String[] {BUILDINGS_ROWID, BUILDINGS_COLLEGE_ID, BUILDINGS_BUILDING, 
			BUILDINGS_NAME, BUILDINGS_LAT, BUILDINGS_LONG};
	
	public static final String MACHINES_TABLE = "machines";
	private static final String MACHINES_ROWID = "_id";
	private static final String MACHINES_BUILDING_ID = "building_id";
	private static final String MACHINES_TYPE = "type";
	private static final String MACHINES_LAT = "lat";
	private static final String MACHINES_LONG = "lng";
	private static final String MACHINES_FLOOR = "floor";
	private static final String MACHINES_COLUMN[] = new String[] {MACHINES_ROWID, MACHINES_BUILDING_ID, MACHINES_TYPE,
			MACHINES_LAT, MACHINES_LONG, MACHINES_FLOOR};
	
	public static final String FOODS_TABLE = "foods";
	private static final String FOODS_ROWID = "_id";
	private static final String FOODS_NAME = "name";
	private static final String FOODS_COLUMN[] = new String[] {FOODS_ROWID, FOODS_NAME};
	
	public static final String BEVERAGES_TABLE = "beverages";
	private static final String BEVERAGES_ROWID = "_id";
	private static final String BEVERAGES_NAME = "name";
	private static final String BEVERAGES_COLUMN[] = new String[] {BEVERAGES_ROWID, BEVERAGES_NAME};
	
	
	public VendabullDBM(Context context)
	{
		this.context = context;
	}
	
	public void open() throws SQLException
	{
		dbHelper = new VendabullDBH(context);
		try
		{
			dbHelper.createDataBase();
			database = dbHelper.openDataBase();
		}
		catch (IOException e)
		{
			Log.d("ioexception", "problems with creating database");
		}
		catch (SQLException e)
		{
			Log.d("sqlexception", "couldn't open database");
		}
	}
	
	public void close()
	{
		dbHelper.checkVersion();
		dbHelper.close();
	}
	
	public Cursor getCursor(String table)
	{
		String columns[];
		if (table.equals(COLLEGES_TABLE))
			columns = COLLEGES_COLUMN;
		else if (table.equals(BUILDINGS_TABLE))
			columns = BUILDINGS_COLUMN;
		else if (table.equals(MACHINES_TABLE))
			columns = MACHINES_COLUMN;
		else if (table.equals(FOODS_TABLE))
			columns = FOODS_COLUMN;
		else
			columns = BEVERAGES_COLUMN;
		Cursor cur = database.query(table, columns, null, null, null, null, null);
		return cur;
	}
	
	public Cursor getColleges(long id) throws SQLException
	{
		String selection = COLLEGES_ROWID + "=" + id;
		Cursor cur = database.query(COLLEGES_TABLE, COLLEGES_COLUMN, selection, null, null, null, null);
		if (cur != null)
		{
			cur.moveToFirst();
		}
		return cur;
	}
	
	public Cursor getBuildings(long id) throws SQLException
	{
		String selection = BUILDINGS_ROWID + "=" + id;
		Cursor cur = database.query(BUILDINGS_TABLE, BUILDINGS_COLUMN, selection, null, null, null, null);
		if (cur != null)
		{
			cur.moveToFirst();
		}
		return cur;
	}
	
	public Cursor getMachines(long id) throws SQLException
	{
		String selection = MACHINES_ROWID + "=" + id;
		Cursor cur = database.query(MACHINES_TABLE, MACHINES_COLUMN, selection, null, null, null, null);
		if (cur != null)
		{
			cur.moveToFirst();
		}
		return cur;
	}
	
	public Cursor getMachinesByBuilding(long id) throws SQLException
	{
		String query = "SELECT * FROM " + MACHINES_TABLE + " WHERE " + MACHINES_BUILDING_ID + " = " + id;
		//Cursor cur = database.query(BUILDINGS_TABLE, BUILDINGS_COLUMN, selection, null, null, null, null);
		//Cursor cur = database.query(MACHINES_TABLE, columns, selection, null, null, null, null);
		Cursor cur = database.rawQuery(query, null);
		if (cur != null)
		{
			cur.moveToFirst();
		}
		return cur;
	}
	
	public Cursor getMachinesByCollege(long id) throws SQLException
	{
		String query = "SELECT machines.lat, machines.lng FROM " + BUILDINGS_TABLE + " JOIN " + MACHINES_TABLE +
				" ON buildings._id = machines.building_id WHERE buildings.college_id = " + id;
		Cursor cur = database.rawQuery(query, null);
		if (cur != null)
		{
			cur.moveToFirst();
		}
		return cur;
	}
	
	public Cursor getMachinesByFood(long id) throws SQLException
	{
		String query = "SELECT machines.lat, machines.lng FROM " +MACHINES_TABLE+ " WHERE " +MACHINES_TYPE+ " = 'food' AND product LIKE '%;" +id+ ";%'";
		Cursor cur = database.rawQuery(query, null);
		return cur;
	}
	
	public Cursor getMachinesByBeverage(long id) throws SQLException
	{
		String query = "SELECT machines.lat, machines.lng FROM " +MACHINES_TABLE+ " WHERE " +MACHINES_TYPE+ " = 'beverage' AND product LIKE '%;" +id+ ";%'";
		Cursor cur = database.rawQuery(query,  null);
		return cur;
	}
}
